-- exec sp_change_lot_location 15711, 27, 0

ALTER PROC sp_change_lot_location 

@LOT_ID 	INT, 
@NEWLOCATION_ID INT, 
@FLAG 		INT 

AS
BEGIN

DECLARE @LOT_NUMBER	VARCHAR(50)
DECLARE @FAB_LOT_NUMBER VARCHAR(50)
DECLARE @LOT_STATUS	VARCHAR(50)
DECLARE @NEWLOT_STATUS 	VARCHAR(50)
DECLARE @DATECODE 	VARCHAR(50)
DECLARE @LOTCOMMENTS 	VARCHAR(2000)
DECLARE @PROCESSTYPE 	VARCHAR(50)
DECLARE @PS_TYPE	VARCHAR(50)
DECLARE @PROCESSSTEP_ID INT
DECLARE @NEWLOT_ID 	INT
DECLARE @QTYIN 		INT
DECLARE @WAFER_QTY_IN	INT
DECLARE @LOCATION_ID 	INT
DECLARE @LOT_COST 	FLOAT
DECLARE @TIMESTAMP 	DATETIME
DECLARE @MAX_TIMESTAMP 	DATETIME
DECLARE @DATEIN		DATETIME
	
	SELECT @LOT_NUMBER = LOT_NUMBER, @LOCATION_ID = LOCATION_ID,@FAB_LOT_NUMBER = FAB_LOT_NUMBER,@PROCESSSTEP_ID = PROCESSSTEP_ID, @LOT_STATUS = LOT_CURRENT_STATUS, @DATECODE = LOT_DATE_CODE, @LOTCOMMENTS = CONVERT( CHAR, LOT_COMMENTS), @LOT_COST = LOT_COST, @TIMESTAMP = LOT_TABLE.TIMESTAMP FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
	SELECT @PROCESSTYPE = PS_TYPE_NAME,@PS_TYPE = PS_TYPE FROM PROCESS_STEP WHERE @PROCESSSTEP_ID = PROCESSSTEP_ID

	SELECT @NEWLOT_ID = LOT_ID, @NEWLOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @FAB_LOT_NUMBER AND PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = @NEWLOCATION_ID
	
	IF @newLot_Id IS NULL
	BEGIN
		update lot_table set LOCATION_ID=@NEWLOCATION_ID WHERE LOT_ID=@LOT_ID
		UPDATE LOT_TABLE_HISTORY SET LOCATION_ID=@NEWLOCATION_ID WHERE LOT_ID=@LOT_ID
			--UPDATE LOT_TABLE SET LOT_ID = @NEWLOT_ID WHERE LOT_ID = @LOT_ID
	END
	ELSE
	BEGIN
		-- DUMMY UPDATE TO AVOID SQLEXCEPTION
   		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
	END

END

